package com.example.a_java.service.impl;

import com.example.a_java.service.DbInitService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.io.ClassPathResource;
import org.springframework.jdbc.datasource.init.ResourceDatabasePopulator;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.Map;

/**
 * 数据库初始化服务实现类
 */
@Service
@Slf4j
public class DbInitServiceImpl implements DbInitService {

    @Autowired
    private DataSource dataSource;

    // 需要检查的表列
    private static final String[] REQUIRED_TABLES = {
        "loan_overdue",
        "loan_collection_record",
        "loan_repayment_reminder",
        "loan_repayment_record",
        "loan_repayment_plan"
    };
    
    // 需要检查的统计表列
    private static final String[] REQUIRED_STATS_TABLES = {
        "stats_report_cache",
        "stats_business",
        "stats_loan",
        "stats_risk",
        "stats_lending",
        "stats_lending_failure"
    };

    @Override
    public Map<String, Boolean> checkTablesExist() {
        Map<String, Boolean> tableStatus = new LinkedHashMap<>();
        
        try (Connection connection = dataSource.getConnection()) {
            DatabaseMetaData metaData = connection.getMetaData();
            
            // 检查每个表是否存在
            for (String tableName : REQUIRED_TABLES) {
                try (ResultSet tables = metaData.getTables(null, null, tableName, new String[] {"TABLE"})) {
                    tableStatus.put(tableName, tables.next());
                }
            }
        } catch (SQLException e) {
            log.error("检查数据库表时发生错误", e);
            // 如果发生异常，将所有表标记为不存在
            for (String tableName : REQUIRED_TABLES) {
                tableStatus.put(tableName, false);
            }
        }
        
        return tableStatus;
    }

    @Override
    @Transactional
    public int initTables() {
        int tablesCreated = 0;
        
        try {
            log.info("开始初始化数据库表...");
            
            // 执行SQL脚本创建
            executeScript("db/repayment_tables.sql");
            
            // 检查表是否创建成功
            Map<String, Boolean> tableStatus = checkTablesExist();
            tablesCreated = (int) tableStatus.values().stream().filter(exists -> exists).count();
            
            log.info("成功创建 {} 个表", tablesCreated);
        } catch (Exception e) {
            log.error("初始化数据库表时发生错误", e);
            throw new RuntimeException("初始化数据库表失败", e);
        }
        
        return tablesCreated;
    }

    @Override
    @Transactional
    public int initSampleData() {
        int recordsInserted = 0;
        
        try {
            log.info("开始初始化示例数据...");
            
            // 执行SQL脚本插入示例数据
            executeScript("db/repayment_sample_data.sql");
            
            // 这里应该返回插入的记录数，但由于我们无法直接获取，先用一个估计
            recordsInserted = 29; // 根据sample_data.sql中的插入语句数量
            
            log.info("成功插入了 {} 条示例数据记录", recordsInserted);
        } catch (Exception e) {
            log.error("初始化示例数据时发生错误", e);
            throw new RuntimeException("初始化示例数据失败", e);
        }
        
        return recordsInserted;
    }

    @Override
    @Transactional
    public Map<String, Object> initAll() {
        Map<String, Object> result = new HashMap<>();
        
        try {
            // 1. 初始化表结构
            int tablesCreated = initTables();
            result.put("tablesCreated", tablesCreated);
            
            // 2. 初始化示例数据
            int recordsInserted = initSampleData();
            result.put("recordsInserted", recordsInserted);
            
            result.put("success", true);
            log.info("数据库初始化完成: 创建了 {} 个表，插入了 {} 条记录", tablesCreated, recordsInserted);
        } catch (Exception e) {
            log.error("执行完整初始化时发生错误", e);
            result.put("success", false);
            result.put("error", e.getMessage());
        }
        
        return result;
    }
    
    @Override
    public Map<String, Boolean> checkStatsTablesExist() {
        Map<String, Boolean> tableStatus = new LinkedHashMap<>();
        
        try (Connection connection = dataSource.getConnection()) {
            DatabaseMetaData metaData = connection.getMetaData();
            
            // 检查每个统计表是否存在
            for (String tableName : REQUIRED_STATS_TABLES) {
                try (ResultSet tables = metaData.getTables(null, null, tableName, new String[] {"TABLE"})) {
                    tableStatus.put(tableName, tables.next());
                }
            }
        } catch (SQLException e) {
            log.error("检查统计数据库表时发生错误", e);
            // 如果发生异常，将所有表标记为不存在
            for (String tableName : REQUIRED_STATS_TABLES) {
                tableStatus.put(tableName, false);
            }
        }
        
        return tableStatus;
    }
    
    @Override
    @Transactional
    public int initStatsTables() {
        int tablesCreated = 0;
        
        try {
            log.info("开始初始化统计数据库表...");
            
            // 执行SQL脚本创建
            executeScript("db/statistics_tables.sql");
            
            // 检查表是否创建成功
            Map<String, Boolean> tableStatus = checkStatsTablesExist();
            tablesCreated = (int) tableStatus.values().stream().filter(exists -> exists).count();
            
            log.info("成功创建 {} 个统计表", tablesCreated);
        } catch (Exception e) {
            log.error("初始化统计数据库表时发生错误", e);
            throw new RuntimeException("初始化统计数据库表失败", e);
        }
        
        return tablesCreated;
    }
    
    @Override
    @Transactional
    public int initStatsSampleData() {
        int recordsInserted = 0;
        
        try {
            log.info("开始初始化统计示例数据...");
            
            // 如果有统计示例数据脚本，则执行
            // executeScript("db/statistics_sample_data.sql");
            
            // 目前没有统计示例数据，先返回0
            recordsInserted = 0;
            
            log.info("统计示例数据初始化完成");
        } catch (Exception e) {
            log.error("初始化统计示例数据时发生错误", e);
            throw new RuntimeException("初始化统计示例数据失败", e);
        }
        
        return recordsInserted;
    }
    
    @Override
    @Transactional
    public Map<String, Object> initStatsAll() {
        Map<String, Object> result = new HashMap<>();
        
        try {
            // 1. 初始化统计表结构
            int tablesCreated = initStatsTables();
            result.put("tablesCreated", tablesCreated);
            
            // 2. 初始化统计示例数据
            int recordsInserted = initStatsSampleData();
            result.put("recordsInserted", recordsInserted);
            
            result.put("success", true);
            log.info("统计数据库初始化完成: 创建了 {} 个表，插入了 {} 条记录", tablesCreated, recordsInserted);
        } catch (Exception e) {
            log.error("执行统计模块完整初始化时发生错误", e);
            result.put("success", false);
            result.put("error", e.getMessage());
        }
        
        return result;
    }
    
    /**
     * 执行SQL脚本
     * @param scriptPath 脚本路径
     */
    private void executeScript(String scriptPath) {
        try {
            ResourceDatabasePopulator populator = new ResourceDatabasePopulator();
            populator.addScript(new ClassPathResource(scriptPath));
            populator.execute(dataSource);
            log.info("成功执行SQL脚本: {}", scriptPath);
        } catch (Exception e) {
            log.error("执行SQL脚本时发生错误", scriptPath, e);
            throw new RuntimeException("执行SQL脚本失败: " + scriptPath, e);
        }
    }
} 
